DROP TABLE IF EXISTS jms_dim.dim_lmdm_sys_customer;
CREATE EXTERNAL TABLE jms_dim.dim_lmdm_sys_customer(
 ID bigint comment "",  --NUMBER(11)
 CODE string comment "客户编码",  --NVARCHAR2(20)
 NAME string comment "客户名称",  --NVARCHAR2(200)
 ABBREVIATION string comment "简称",  --NVARCHAR2(255)
 INDUSTRY_ID bigint comment "行业类型",  --NUMBER(11)
 TYPE_ID bigint comment "客户类型",  --NUMBER(11)
 COUNTRY_ID bigint comment "国家ID",  --NUMBER(11)
 COUNTRY_CODE string comment "国家代码",  --NVARCHAR2(10)
 PROVINCE_ID bigint comment "省份ID",  --NUMBER(11)
 CITY_ID bigint comment "城市ID",  --NUMBER(11)
 AREA_ID bigint comment "区域ID",  --NUMBER(11)
 ADDRESS string comment "地址",  --NVARCHAR2(200)
 IS_ENABLE int comment "是否启用:1启用,2不启用",  --NUMBER(6)
 IS_DELETE int comment "是否删除:1未删除,2已删除",  --NUMBER(6)
 CREATE_BY bigint comment "创建人ID",  --NUMBER(11)
 UPDATE_BY bigint comment "最后更新人ID",  --NUMBER(11)
 CREATE_BY_NAME string comment "创建人名称",  --NVARCHAR2(50)
 UPDATE_BY_NAME string comment "最后修改人名称",  --NVARCHAR2(50)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 UPDATE_TIME timestamp comment "更新时间",  --DATE
 VERSION string comment "版本号",  --NVARCHAR2(10)
 SORT int comment "排序",  --NUMBER(6)
 NATURE_ID bigint comment "客户性质:国有、民营、股份制、外资、社会组织机构",  --NUMBER(11)
 MAIN_DEPOSIT string comment "主要寄托物",  --NVARCHAR2(50)
 CONTACT_NAME string comment "联系人姓名",  --NVARCHAR2(50)
 CONTACT_MOBILE string comment "联系人手机",  --NVARCHAR2(15)
 CONTACT_EMAIL string comment "联系人邮箱",  --NVARCHAR2(200)
 FINANCIAL_NAME string comment "财务对接人姓名",  --NVARCHAR2(50)
 FINANCIAL_MOBILE string comment "财务对接人手机",  --NVARCHAR2(15)
 FINANCIAL_EMAIL string comment "财务对接人邮箱",  --NVARCHAR2(200)
 ZIP_CODE string comment "邮编",  --NVARCHAR2(10)
 ID_CARD_NUMBER string comment "身份证号码",  --NVARCHAR2(100)
 UNIFIED_CREDIT_CODE string comment "统一信用代码",  --NVARCHAR2(18)
 ORGANIZATION_CODE string comment "组织机构代码",  --NVARCHAR2(18)
 TAX_REGISTRATION_CODE string comment "税务登记号",  --NVARCHAR2(25)
 BUSINESS_REGISTRATION_CODE string comment "企业注册号",  --NVARCHAR2(25)
 BUSINESS_LICENSE_IMG_URL string comment "营业执照图片",  --NVARCHAR2(400)
 ORGANIZATION_IMG_URL string comment "组织机构代码证图片",  --NVARCHAR2(100)
 TAX_REGISTRATION_IMG_URL string comment "税务登记证图片",  --NVARCHAR2(100)
 ID_CARD_FRONT_IMG_URL string comment "身份证正面图片",  --NVARCHAR2(200)
 ID_CARD_BACK_IMG_URL string comment "身份证反面图片",  --NVARCHAR2(100)
 NETWORK_ID bigint comment "客户所属网点",  --NUMBER(11)
 STAFF_ID bigint comment "业务员ID;关联员工表",  --NUMBER(11)
 STAFF_MOBILE string comment "业务员手机号",  --NVARCHAR2(15)
 CUSTOMER_LEVEL_ID bigint comment "客户等级：VIP、普通",  --NUMBER(11)
 IS_VIP_ACCOUNT int comment "是否开通VIP系统账户:1-是， 2-否",  --NUMBER(6)
 CREDIT_AMOUNT bigint comment "信用额度:1-999999",  --NUMBER(11)
 IS_OPEN_COD int comment "是否开通COD:1-是;2-否",  --NUMBER(6)
 SINGLE_COD_LIMIT bigint comment "单笔COD限额:1-99999",  --NUMBER(11)
 REBATE_PERIOD_ID bigint comment "返款周期:3、7、15、30天",  --NUMBER(11)
 CC_STT_BANK_NAME string comment "代收结算账户开户行",  --NVARCHAR2(50)
 CC_STT_BANK_ACCOUNT string comment "代收结算账号",  --NVARCHAR2(20)
 CC_STT_BANK_ACCOUNT_NAME string comment "代收结算账户",  --NVARCHAR2(50)
 BILL_ID bigint comment "账单类型:纸质账单、电子账单",  --NUMBER(11)
 BILL_EMIAL string comment "接收账单的EMAIL账号",  --NVARCHAR2(200)
 SETTLEMENT_ID bigint comment "结算方式：寄付月结、寄付现结",  --NUMBER(11)
 CUSTOMER_SHARE_ID bigint comment "共享客户：全网共享、下级共享、不共享",  --NUMBER(11)
 PAY_ID bigint comment "付款方式：现金、转账、支票",  --NUMBER(11)
 PROMPT bigint comment "付款期限:1-999",  --NUMBER(11)
 INVOICE_ID bigint comment "发票类型：增值税发票、增值税普票、普通电子发票",  --NUMBER(11)
 TAXPAYER_NAME string comment "纳税人名称",  --NVARCHAR2(50)
 TAXPAYER_NUMBER string comment "纳税人识别号",  --NVARCHAR2(50)
 TAXPAYER_REGISTRATION_ADDRESS string comment "纳税人注册地址",  --NVARCHAR2(200)
 TAXPAYER_PHONE string comment "纳税人联系电话",  --NVARCHAR2(255)
 TAXPAYER_BANK_NAME string comment "纳税人开户银行名称",  --NVARCHAR2(255)
 TAXPAYER_BANK_ACCOUNT string comment "纳税人开户银行账号",  --NVARCHAR2(255)
 INVOICE_RECIPIENT_NAME string comment "发票接收人姓名",  --NVARCHAR2(255)
 INVOICE_RECIPIENT_PHONE string comment "发票接收人电话",  --NVARCHAR2(255)
 INVOICE_RECIPIENT_EMAIL string comment "发票接收人邮箱",  --NVARCHAR2(200)
 INVOICE_RECIPIENT_ADDRESS string comment "发票接收人地址",  --NVARCHAR2(200)
 SOURCE_ID bigint comment "客户来源",  --NUMBER(11)
 MALL_ID string comment "商家ID（主要对接第三方客户）",  --NVARCHAR2(60)
 MALL_NAME string comment "商家名称",  --NVARCHAR2(60)
 PARENT_CUSTOMER string comment "父客户",  --NVARCHAR2(60)
 METER_BUBBLE bigint comment "计泡参数",  --NUMBER(11)
 PARENT_ID bigint comment "父客户ID",  --NUMBER(11)
 BILLING_DAY bigint comment "账单日：1-31天",  --NUMBER(11)
 ACCOUNT_PERIOD_ID bigint comment "账期类型：月结、周结",  --NUMBER(11)
 QUOTE_ID bigint comment "客户报价方式：标准报价、定制报价",  --NUMBER(11)
 ESS_OBJECT int comment "电子面单对象：2网点，3客户",  --NUMBER(6)
 BILLING_WEIGHT string comment "计费重量取值",  --NVARCHAR2(200)
 IS_MAX_WEIGHT bigint comment "是否最大值取重: 1是，2否",  --NUMBER(11)
 IS_BUSINESS_PARCEL tinyint comment "是否是商务件 1是2否",  --NUMBER(2)
 SHARE_ENABLE tinyint comment "是否全网共享客户,1:全网共享,2:不共享",  --NUMBER(1)
 PRINTER_EQUIPMENT_NUMBER string comment "打印设备编码",  --NVARCHAR2(50)
 PRINTER_EQUIPMENT_MANUFACTURER tinyint comment "打印设备厂商 1-快递100  2-汉印",  --NUMBER(2)
 PRINTER_APPLET_QR_IMG_PATH string comment "小程序二维码",  --NVARCHAR2(100)
 CARD_TYPE tinyint comment "证件类型",  --NUMBER(2)
 ATTRIBUTE_ID int comment "客户属性  普通客户（默认）、农特市场客户、专业市场客户、总部营销推广活动客户",  --NUMBER(8)
 RECALC_COST_FLAG tinyint comment "是否按照揽重重算费用(1:是,2:否)",  --NUMBER(1)
 IS_RECEIPT_FLAG tinyint comment "是否回单标识,1:是,2:否",  --NUMBER(1)
 RECEIPT_FEE decimal(7,2) comment "回单费用",  --NUMBER(7,2)
 SETTLE_CUSTOMER_CODE string comment "结算客户编码",  --NVARCHAR2(20)
 MARKET_STAFF_CODE string comment "市场业务员编码",  --NVARCHAR2(20)
 CATEGORY_PRIMARY_ID bigint comment "主营类目ID",  --NUMBER(11)
 CATEGORY_PRIMARY_NAME string comment "主营类目名称",  --NVARCHAR2(50)
 CATEGORY_FIRST_ID bigint comment "一级类目ID",  --NUMBER(11)
 CATEGORY_FIRST_NAME string comment "一级类目名称",  --NVARCHAR2(50)
 CATEGORY_SECOND_ID bigint comment "二级类目ID",  --NUMBER(11)
 CATEGORY_SECOND_NAME string comment "二级类目名称",  --NVARCHAR2(50)
 DEDUCTIONS_MODE tinyint comment "扣款模式:1不扣款,2运单实时扣款,3运单审核扣款",  --NUMBER(2)
 IS_SHOW_APP smallint comment "是否App显示：0:不显示,1:内场App,2:外场App,3:内外场app都展示",  --NUMBER(3)
 project_name string COMMENT "项目名称"
 )
 COMMENT '客户资料维表'
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_dim.db/external/dim_lmdm_sys_customer'
 TBLPROPERTIES (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );
